import datetime

from flask_login import UserMixin
from sqlalchemy import or_
from sqlalchemy.dialects.sqlite import JSON
from sqlalchemy.sql import func
from werkzeug.security import generate_password_hash, check_password_hash

from app.ext import db,login_manager


def time_to_str(time_obj):
    """将时间对象转换为字符串"""
    if not time_obj:
        return None
    return time_obj.strftime("%Y-%m-%d %H:%M:%S")


class User(db.Model, UserMixin):
    __tablename__ = 'User'

    fid = db.Column(db.Integer, primary_key=True, comment='用户的唯一标识')
    username = db.Column(db.String(64), unique=True, nullable=False, comment='用户名')
    password_hash = db.Column(db.String(64), nullable=False, comment='密码哈希值')
    email = db.Column(db.String(120), unique=True, nullable=False, comment='邮箱')
    avatar = db.Column(db.String(200), default='avatar.png', comment='用户头像')
    is_active = db.Column(db.Boolean, default=True, comment='用户是否激活')
    is_deleted = db.Column(db.Boolean, default=False, comment='是否禁用')
    created_at = db.Column(db.DateTime, server_default=func.now(), comment='用户创建时间')
    updated_at = db.Column(db.DateTime, server_default=func.now(), onupdate=func.now(), comment='用户更新时间')
    last_login_at = db.Column(db.DateTime, nullable=True, comment='最后登录时间')

    def __repr__(self):
        return f"<User(username={self.username})>"

    def get_id(self):
        """必须重写此方法，返回用户的唯一标识符"""
        return str(self.fid)  # 使用fid作为唯一标识，并确保返回字符串

    @login_manager.user_loader
    def load_user(user_id):
        return User.query.get(int(user_id))

    def set_password(self, password):
        """设置密码哈希值"""
        self.password_hash = generate_password_hash(password)

    def check_password(self, password):
        """验证密码"""
        return check_password_hash(self.password_hash, password)
    @classmethod
    def create_user(cls, username, email, password, **kwargs):
        """创建新用户"""
        user = cls(username=username, email=email, **kwargs)
        user.set_password(password)
        db.session.add(user)
        db.session.commit()
        return user

    def to_dict(self):
        """Convert User instance to dictionary"""
        data = {
            'fid': self.fid,
            'username': self.username,
            'email': self.email,
            'avatar': self.avatar,
            'is_active': self.is_active,
            'is_deleted': self.is_deleted,
            'created_at': time_to_str(self.created_at if self.created_at else None),
            'updated_at': time_to_str(self.updated_at if self.updated_at else None),
            'last_login_at': time_to_str(self.last_login_at if self.last_login_at else None),
            'is_admin': self.is_admin
        }

        return data


class PDMFileModel(db.Model):
    __tablename__ = 'pdm_files'

    fid = db.Column(db.Integer, primary_key=True, autoincrement=True, comment='主键')
    file_name = db.Column(db.String(64), unique=True, nullable=False, comment='文件名')
    database_type = db.Column(db.String(64), nullable=False, comment='数据库类型')
    file_md5 = db.Column(db.String(64), nullable=False, comment='文件MD5')
    file_modify_time = db.Column(db.DateTime, nullable=False, comment='文件修改时间')
    file_size = db.Column(db.Integer, nullable=False, comment='文件大小')
    table_count = db.Column(db.Integer, nullable=False, comment='表数量')

    tables = db.relationship('PDMTableModel', backref='pdm_file', lazy=True)
    modules = db.relationship('PDMModuleModel', backref='pdm_file', lazy=True)

    def __repr__(self):
        return f"<PDMFile(file_name={self.file_name})>"
    @classmethod
    def get_all_files(cls):
        return cls.query.all()

    @classmethod
    def get_file_by_id(cls, fid):
        return cls.query.get(fid)
    def to_dict(self):
        # 计算文件大小KB/MB/GB
        file_size = self.file_size
        if file_size < 1024:
            file_size_str = f"{file_size}B"
        elif file_size < 1024 * 1024:
            file_size_str = f"{file_size / 1024:.2f}KB"
        elif file_size < 1024 * 1024 * 1024:
            file_size_str = f"{file_size / (1024 * 1024):.2f}MB"
        else:
            file_size_str = f"{file_size / (1024 * 1024 * 1024):.2f}GB"
        data = {
            'fid': self.fid,
            'file_name': self.file_name,
            'database_type': self.database_type,
            'file_md5': self.file_md5,
            'file_modify_time': time_to_str(self.file_modify_time if self.file_modify_time else None),
            'file_size': file_size_str,
            'table_count': self.table_count,
        }
        return data

class PDMTableModel(db.Model):
    __tablename__ = 'tables'

    fid = db.Column(db.Integer, primary_key=True, autoincrement=True, comment='主键')
    table_id = db.Column(db.String(128), nullable=False, comment='表ID')
    objectId = db.Column(db.String(128), unique=True, nullable=False, comment='表的唯一标识')
    pdm_id = db.Column(db.Integer, db.ForeignKey('pdm_files.fid'), nullable=False, comment='所属文件')
    owner = db.Column(db.String(64), nullable=False, comment='表实体')
    name = db.Column(db.String(64), nullable=False, comment='表名')
    code = db.Column(db.String(64), nullable=False, comment='表代码')
    description = db.Column(db.Text, comment='表描述')
    modifier = db.Column(db.String(64), nullable=False, comment='修改人')
    modificationDate = db.Column(db.DateTime, nullable=False, comment='修改时间')
    creationDate = db.Column(db.DateTime, nullable=False, comment='创建时间')
    creator = db.Column(db.String(64), nullable=False, comment='创建人')

    columns = db.relationship('PDMColumnModel', backref='table', lazy=True)

    def __repr__(self):
        return f"<Table(name={self.name}, code={self.code})>"

    @classmethod
    def get_tables(cls, table_id,pdm_id):
        table=cls.query.filter_by(table_id=table_id,pdm_id=pdm_id).first()
        return table

    # 表名搜索
    @classmethod
    def search_tables(cls, table_name):
        # 边界条件检查
        if not isinstance(table_name, str) or not table_name.strip():
            print(table_name)
            raise ValueError("table_name must be a non-empty string")

        try:
            # 使用参数化查询避免 SQL 注入
            query_conditions = (
                    cls.name.like(f'%{table_name}%') |
                    cls.code.like(f'%{table_name}%')
            )
            tables = cls.query.filter(query_conditions).all()
            return tables
        except Exception as e:
            # 异常处理，记录日志并返回空列表
            print(f"Database error occurred: {e}")
            return []

    def to_dict(self):
        data = {
            'fid': self.fid,
            'table_id': self.table_id,
            'objectId': self.objectId,
            'pdm_id': self.pdm_id,
            'owner': self.owner,
            'name': self.name,
            'code': self.code,
            'description': self.description,
            'modifier': self.modifier,
            'modificationDate': self.modificationDate,
            'creationDate': self.creationDate,
            'creator': self.creator,
        }
        return data

class PDMColumnModel(db.Model):
    __tablename__ = 'columns'

    fid = db.Column(db.Integer, primary_key=True, autoincrement=True, comment='主键')
    objectId = db.Column(db.String(128), unique=True, nullable=False, comment='列的唯一标识')
    table_id = db.Column(db.String(64), db.ForeignKey('tables.objectId'), nullable=False, comment='所属表')
    name = db.Column(db.String(64), nullable=False, comment='列名')
    code = db.Column(db.String(64), nullable=False, comment='列代码')
    data_type = db.Column(db.String(64), nullable=False, comment='数据类型')
    pdm_id = db.Column(db.Integer, db.ForeignKey('pdm_files.fid'), nullable=False, comment='所属文件')

    mandatory = db.Column(db.String(64), nullable=False, comment='是否必填')
    comment = db.Column(db.Text, comment='列描述')
    default = db.Column(db.String(64), nullable=True, comment='默认值')
    length = db.Column(db.String(64), nullable=True, comment='长度')


    def __repr__(self):
        return f"<Column(name={self.name}, type={self.data_type})>"




    @classmethod
    def get_table_id_columns(cls, table_id,pdm_id):
        columns = cls.query.filter_by(table_id=table_id, pdm_id=pdm_id).all()
        return columns
    def to_dict(self):
        data = {
            'fid': self.fid,
            'objectId': self.objectId,
            'table_id': self.table_id,
            'name': self.name,
            'code': self.code,
            'data_type': self.data_type,
            'mandatory': self.mandatory,
            'comment': self.comment,
            'default': self.default,
            'length': self.length,
            'pdm_id': self.pdm_id,
        }
        return data



class PDMModuleModel(db.Model):
    __tablename__ = 'modules'

    fid = db.Column(db.Integer, primary_key=True, autoincrement=True, comment='主键')
    m_objectId = db.Column(db.String(128), nullable=False, comment='模块的父唯一标识')
    m_name = db.Column(db.String(64), nullable=False, comment='模块实体')
    m_code = db.Column(db.String(64), nullable=False, comment='模块代码')
    phy_objectId = db.Column(db.String(128), unique=True, nullable=False, comment='物理模块标识')
    phy_name = db.Column(db.String(64), nullable=False, comment='物理模块实体')
    phy_code = db.Column(db.String(64), nullable=False, comment='物理模块代码')
    pdm_id = db.Column(db.Integer, db.ForeignKey('pdm_files.fid'), nullable=False, comment='所属文件')
    description = db.Column(db.Text, comment='模块描述')
    table_ids = db.Column(JSON, comment='模块下的表 ID 列表')

    def __repr__(self):
        return f"<Module(name={self.phy_name}, code={self.phy_code})>"


    @classmethod
    def get_packages(cls, pdm_id):
        # 搜索对应文件的package
        packages = cls.query.filter_by(pdm_id=pdm_id).all()
        return packages

    @classmethod
    def get_package_tables(cls,fid,pdm_id):
        package = cls.query.filter_by(pdm_id=pdm_id, fid=fid).first()

        return package
    def to_dict(self):
        data = {
            'fid': self.fid,
            "m_objectId": self.m_objectId,
            "m_name": self.m_name,
            "m_code": self.m_code,
            "phy_objectId": self.phy_objectId,
            "phy_name": self.phy_name,
            "phy_code": self.phy_code,
            "pdm_id": self.pdm_id,
            "description": self.description,
            "table_ids": self.table_ids,
            "table_count": len(self.table_ids)
        }
        return data
